http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.framework.adonet/2006-03/msg01030.html




***** BEGIN CODE CLASS *****

Imports System
Imports System.Data
Imports System.Data.OracleClient
Imports System.Windows.Forms
Imports System.IO
Imports System.Collections.Generic

Public Class BlobImports

Public Sub DoBlob(ByVal blobPath As String, ByVal blobFilename As String, 
ByVal blobID As String)

'create connection
Dim cnBlob As New OracleClient.OracleConnection("Data 
Source=database;Persist Security Info=True;User 
ID=user;Password=pword;Unicode=True")
Try
With cnBlob
' open connection
.Open()

'create command object
Dim cmdBlob As OracleClient.OracleCommand = cnBlob.CreateCommand()

'select text
cmdBlob.CommandText = "SELECT BLOB_ID, BLOB_DESC FROM BLOBS WHERE 
BLOB_DESC = :pBlobDesc"
'add parameter
cmdBlob.Parameters.Add(":pBlobDesc", OracleType.VarChar)
cmdBlob.Parameters(0).Value = blobFilename

'read records
ReadBlobRecords(cmdBlob)

'update command text for update
cmdBlob.CommandText = _
"SELECT BLOB_DESC, BLOB FROM BLOBS WHERE BLOB_DESC = :pBlobDesc 
FOR UPDATE"

'update record with blob item - causes error in createtempblob 
function
WriteBlob(cmdBlob, blobPath, blobFilename)

End With
Catch ex As Exception
MsgBox(ex.ToString & vbCrLf & ex.Message, MsgBoxStyle.OkOnly, "Foo")
Finally
cnBlob.Close()
End Try
End Sub

Private Sub ReadBlobRecords(ByVal blobCommand As OracleClient.OracleCommand)
Dim blobTrans As OracleClient.OracleTransaction = 
blobCommand.Connection.BeginTransaction
blobCommand.Transaction = blobTrans
Dim blobReader As OracleClient.OracleDataReader = 
blobCommand.ExecuteReader()
While blobReader.Read
lbBlobs.Items.Add(blobReader.GetOracleString(1))
End While
blobTrans.Commit()
End Sub

Private Sub WriteBlob(ByVal blobCommand As OracleClient.OracleCommand, 
ByVal blobPath As String, ByVal blobName As String)

'read file into binary
Dim fi As New FileInfo(blobpath & "\" & blobname)
Dim sr As New StreamReader(fi.FullName)
Dim tempBuffer As String = sr.ReadToEnd
sr.Close()

'begin transaction to post blobs
Dim blobTrans As OracleClient.OracleTransaction = 
blobCommand.Connection.BeginTransaction
blobCommand.Transaction = blobTrans
Dim blobReader As OracleClient.OracleDataReader = 
blobCommand.ExecuteReader
While blobReader.Read
Dim oBlobFile As OracleClient.OracleLob = blobReader.GetOracleLob(1)
' call to create temp blob throws oracle error
Dim inBlobFile As OracleClient.OracleLob = CreateTempLob(blobCommand, 
oBlobFile.LobType)
Dim lngBlob As Long = oBlobFile.CopyTo(inBlobFile)
blobTrans.Commit()
End While

End Sub

Private Function CreateTempLob(ByVal blobCmd As 
OracleClient.OracleCommand, ByVal blobType As OracleClient.OracleType)
blobCmd.CommandText = _
"DECLARE A " & blobType & "; BEGIN DBMS_LOB.CREATETEMPORARY(A, FALSE); 
:tmpBlob = A;"
Dim pBlobTemp As OracleParameter = blobCmd.Parameters.Add("tmpBlob", 
blobType)
''pBlobTemp.Value = Nothing
pBlobTemp.Direction = ParameterDirection.Output
' here is where the error is occuring 
blobCmd.ExecuteNonQuery()
Return pBlobTemp.Value
End Function

***** END CODE CLASS ***** 